﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Security.Cryptography;
using System.Data.SqlServerCe;

using Modelo_ETdA.Negocio.Analista;

namespace Modelo_ETdA.Dados
{
    public class DadosAnalista
    {
        public DadosAnalista()
        {
        }

        private string getMd5Hash(string str)
        {
            // Create a new instance of the MD5CryptoServiceProvider object.
            MD5 md5Hasher = MD5.Create();

            // Convert the input string to a byte array and compute the hash.
            byte[] data = md5Hasher.ComputeHash(Encoding.Default.GetBytes(str));

            // Create a new Stringbuilder to collect the bytes
            // and create a string.
            StringBuilder sBuilder = new StringBuilder();

            // Loop through each byte of the hashed data 
            // and format each one as a hexadecimal string.
            for (int i = 0; i < data.Length; i++)
            {
                sBuilder.Append(data[i].ToString("x2"));
            }

            // Return the hexadecimal string.
            return sBuilder.ToString();
        }

        public int efectuarLogin(String username, String password)
        {
            password = getMd5Hash(password);
            SqlCeDataReader dataReader = null;
            try
            {
                string cmdstr = "select cod from Analista where username='" + username + "' AND password='" + password + "'";
                SqlCeCommand comando = AdminBD.getConCe().CreateCommand();
                comando.CommandText = cmdstr;
                // new SqlCommand(cmdstr, AdminBD.getCon());
                dataReader = comando.ExecuteReader();
                if (dataReader.Read())
                {
                    dataReader.Close();
                    return 0;
                }
                dataReader.Close();
            }
            catch (SqlCeException e)
            {
                Console.WriteLine(e.ToString());
                if (dataReader != null)
                    if (!(dataReader.IsClosed)) dataReader.Close();
            }
            return 1;
        }

        public int efectuarRegisto(String prim_nome, String ult_nome, String bi, String email, String username, String password)
        {
            password = getMd5Hash(password);

            //Verificar se o utilizador já se encontra registado
            SqlCeDataReader dataReader = null;
            SqlCeCommand comando;
            try
            {
                comando = new SqlCeCommand("select cod from Analista where username='" + username + "' OR BI=" + bi + " OR email='" + email + "'", AdminBD.getConCe()); ;
                dataReader = comando.ExecuteReader();
                if (dataReader.Read())
                {
                    dataReader.Close();
                    return 1; //utilizador ja se encontra registado
                }
                dataReader.Close();
            }
            catch (SqlCeException e)
            {
                Console.WriteLine(e.ToString());
                if (dataReader != null)
                    if (!(dataReader.IsClosed)) dataReader.Close();
                return 2;
            }

            //Efectuar Registo
            SqlCeTransaction trans = null;
            try
            {
                trans = AdminBD.getConCe().BeginTransaction();
                string cmdstr = "insert into Analista (primeiro_nome, ultimo_nome, email, bi, username, password,data_registo) values ('" + prim_nome + "','" + ult_nome + "','" + email + "'," + bi + ",'" + username + "','" + password + "', GETDATE())";
                comando.Transaction = trans;
                comando = new SqlCeCommand(cmdstr, AdminBD.getConCe());
                comando.ExecuteNonQuery();
                trans.Commit();
            }
            catch (SqlCeException e)
            {
                Console.WriteLine(e.ToString());
                trans.Rollback();
                return 2;
            }
            return 0;
        }

        public Analista getAnalista(String username)
        {
            SqlCeDataReader reader = null;
            SqlCeCommand cmd;
            try
            {
                SqlCeConnection conn = AdminBD.getConCe();
                cmd = new SqlCeCommand("select ana.cod,ana.primeiro_nome,ana.ultimo_nome,ana.BI,ana.email,ana.data_registo from Analista ana where ana.username='" + username + "'", conn);
                int cod = 0;
                String pn = "", un = "", mail = "", bi = "";
                DateTime dt = new DateTime();
                reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    cod = reader.GetInt32(0);
                    pn = reader.GetString(1);
                    un = reader.GetString(2);
                    bi = reader.GetString(3);
                    mail = reader.GetString(4);
                    dt = reader.GetDateTime(5);
                }
                reader.Close();

                cmd = new SqlCeCommand("select codAmbC from AnalistaAmbComercial where codAnalista=" + cod, conn);
                reader = cmd.ExecuteReader();
                List<int> lcodAmb = new List<int>();

                while (reader.Read())
                {
                    lcodAmb.Add(reader.GetInt32(0));
                }
                reader.Close();
                return new Analista(cod, pn, un, mail, bi.ToString(), username, dt, lcodAmb);
            }
            catch (SqlCeException e)
            {
                Console.WriteLine(e.ToString());
                if (reader != null)
                    if (!(reader.IsClosed)) reader.Close();
                return null;
            }
        }

        public int efectuarAltPassword(String bi, String email, String username, String novapass)
        {
            novapass = getMd5Hash(novapass);
            SqlCeDataReader dataReader = null;
            //verificar se o utilizador se encontra registado
            SqlCeCommand comando;
            try
            {
                string cmdstr = "select cod from Analista where username='" + username + "' AND BI=" + bi + " AND email='" + email + "'";
                comando = new SqlCeCommand(cmdstr, AdminBD.getConCe());
                dataReader = comando.ExecuteReader();
                if (dataReader.Read())
                {
                    dataReader.Close();
                }
                else
                {
                    dataReader.Close();
                    return 1;
                }
            }
            catch (SqlCeException e)
            {
                Console.WriteLine(e.ToString());
                if (dataReader != null)
                    if (!(dataReader.IsClosed)) dataReader.Close();
                return 2;
            }

            //efectuar alteracao pass
            SqlCeTransaction trans = null;
            try
            {
                trans = AdminBD.getConCe().BeginTransaction();
                string cmdstr = "update Analista set password='" + novapass + "' where username='" + username + "'";
                comando = new SqlCeCommand(cmdstr, AdminBD.getConCe());
                comando.ExecuteNonQuery();
                trans.Commit();
            }
            catch (SqlCeException e)
            {
                Console.WriteLine(e.ToString());
                trans.Rollback();
                return 2;
            }
            return 0;
        }

        public int efectuarEditAnalista(String nameLogado, String primeiro_nome, String ultimo_nome, String email, String bi)
        {
            SqlCeCommand comando;
            SqlCeTransaction trans = null;
            try
            {
                trans = AdminBD.getConCe().BeginTransaction();
                string cmdstr = "update Analista set primeiro_nome='" + primeiro_nome + "', ultimo_nome='" + ultimo_nome + "', email='" + email + "', bi=" + bi + "where username='" + nameLogado + "'";
                comando = new SqlCeCommand(cmdstr, AdminBD.getConCe());
                comando.ExecuteNonQuery();
                trans.Commit();
            }
            catch (SqlCeException e)
            {
                Console.WriteLine(e.ToString());
                trans.Rollback();
                return 2;
            }
            return 0;
        }

        public SortedDictionary<String, int> getMapUsernameAnalista()
        {
            SortedDictionary<String, int> userAnalista = new SortedDictionary<String, int>();
            int cod;
            string nome;
            SqlCeDataReader dataReader = null;
            try
            {
                string cmdstr = "select cod,primeiro_nome,ultimo_nome from Analista order by primeiro_nome,ultimo_nome";
                SqlCeCommand comando = new SqlCeCommand(cmdstr, AdminBD.getConCe());
                dataReader = comando.ExecuteReader();
                while (dataReader.Read())
                {
                    cod = (int)dataReader["cod"];
                    nome = dataReader["primeiro_nome"].ToString() + " " + dataReader["ultimo_nome"].ToString();
                    userAnalista.Add(nome, cod);
                }
                dataReader.Close();
            }
            catch (SqlCeException e)
            {
                Console.WriteLine(e.ToString());
                if (dataReader != null)
                    if (!(dataReader.IsClosed)) dataReader.Close();
            }
            return userAnalista;
        }

        public DataTable getDadosTabelaMenu(int codAnalista)
        {

            DataTable dt = new DataTable();
            try
            {
                using (SqlCeDataAdapter a = new SqlCeDataAdapter(
                    "select ac.cod,ac.nome,ac.data_registo,ac.ultima_edicao FROM AmbienteComercial ac,AnalistaAmbComercial analistac where ac.cod=analistac.codAmbC and analistac.codAnalista=" + codAnalista, AdminBD.getConCe()))
                {
                    a.Fill(dt);
                }
            }
            catch (SqlCeException e)
            {
                Console.WriteLine(e.ToString());
            }
            return dt;
        }

    }
}
